import os
import sqlite3
import uuid
from io import BytesIO
from pathlib import Path
from typing import Optional, List, Dict, Any, Tuple
from aiohttp import web
from pydantic import BaseModel, validator, Field

from .handler import ImageService
from ..core.database.connection import db_manager
import aiofiles.os as aio_os
import aiofiles
from PIL import Image
from .utils.task_manager import task_manager
import traceback
import asyncio
import inspect
from urllib.parse import unquote

# 数据模型验证
class RatingRange(BaseModel):
    value: Optional[List[float]] = None

    @validator('value')
    def check_rating_range(cls, v):
        if v is not None:
            if len(v) != 2:
                raise ValueError('评分范围必须是包含两个元素的数组')
            if not all(x >= 0 for x in v):
                raise ValueError('评分值必须是非负数字')
        return v


class AdjacentRequest(BaseModel):
    current_id: str
    direction: str
    value: Optional[List[float]] = None
    class_type: Optional[str] = None

    @validator('direction')
    def check_direction(cls, v):
        if v not in ['prev', 'next']:
            raise ValueError('方向必须是 prev 或 next')
        return v

    @validator('class_type')
    def check_class_type(cls, v, values):
        if 'current_id' in values and values['current_id'] == "" and not v:
            raise ValueError('当 current_id 为 "" 时，必须提供 class_type 参数')
        return v


class BatchUpdateRequest(BaseModel):
    ids: List[str] = Field(..., min_items=1)

    @validator('ids')
    def check_ids_not_empty(cls, v):
        if not v:
            raise ValueError('ID列表不能为空')
        return v


# 通用工具函数
def dict_from_row(row: sqlite3.Row) -> Dict[str, Any]:
    """将sqlite3.Row转换为字典"""
    if row is None:
        return None
    return {key: row[key] for key in row.keys()}


# 业务逻辑服务
class PromptItemService:

    IMAGE_EXTENSIONS = {'.jpg', '.jpeg', '.png','.webp'}
    TEXT_EXTENSIONS = {'.txt'}

    @staticmethod
    def generate_uuid_hex() -> str:
        """生成类似SQLite hex(randomblob(16))的32位十六进制UUID"""
        # 生成16字节的随机数据并转换为十六进制字符串
        return uuid.uuid4().hex.upper()  # 使用大写字母与SQLite保持一致

    @staticmethod
    async def get_image(id: str) -> Dict[str, Any]:
        """获取条目图片"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(
                'SELECT image FROM prompt_items WHERE id = ?',
                [id]
            )
            result = cursor.fetchone()

            if not result:
                raise web.HTTPNotFound(reason="条目不存在")

            return {"result": result['image']}

    @staticmethod
    async def delete_item(id: str) -> Dict[str, Any]:
        """删除条目"""
        with db_manager.get_connection() as conn:
            cursor = conn.execute(
                'DELETE FROM prompt_items WHERE id = ?',
                [id]
            )

            if cursor.rowcount == 0:
                raise web.HTTPNotFound(reason="条目不存在")

            return {"result": cursor.rowcount}

    @staticmethod
    async def delete_disliked_item() -> Dict[str, Any]:
        with db_manager.get_connection() as conn:
            cursor = conn.execute(
                'DELETE FROM prompt_items WHERE disliked = ?',
                '1'
            )

            if cursor.rowcount == 0:
                raise web.HTTPNotFound(reason="条目不存在")

            return {"result": cursor.rowcount}

    @staticmethod
    async def update_item(data: dict) -> Dict[str, Any]:
        """更新条目信息"""
        item_id = data.get('id')
        if not item_id:
            raise web.HTTPBadRequest(reason="必须提供有效的条目ID")

        # 定义可更新字段
        updatable_fields = [
            'class_type', 'name', 'customize_name', 'image',
            'tags', 'description', 'rating', 'favorite', 'disliked'
        ]

        # 检查是否有可更新字段
        has_updatable = any(field in data for field in updatable_fields)
        if not has_updatable:
            raise web.HTTPBadRequest(reason='至少需要提供一个可修改字段')

        updates = ['updated_at = CURRENT_TIMESTAMP']  # 自动更新修改时间
        params: List[Any] = []

        # 动态构建更新字段
        for field in updatable_fields:
            if field in data:
                updates.append(f"{field} = ?")

                # 特殊处理布尔值
                if field in ['favorite', 'disliked']:
                    params.append(1 if data[field] else 0)
                else:
                    params.append(data[field])

        params.append(item_id)

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            try:
                cursor = conn.execute(
                    f"UPDATE prompt_items SET {', '.join(updates)} WHERE id = ?",
                    params
                )

                if cursor.rowcount == 0:
                    raise web.HTTPNotFound(reason="条目不存在")

                # 获取更新后的完整数据
                updated = conn.execute(
                    'SELECT * FROM prompt_items WHERE id = ?',
                    [item_id]
                ).fetchone()

                return {"data": dict_from_row(updated)}

            except sqlite3.IntegrityError as e:
                if 'UNIQUE constraint' in str(e):
                    raise web.HTTPConflict(reason="数据冲突：名称已存在")
                raise web.HTTPConflict(reason=str(e))

    @staticmethod
    async def get_all_items() -> Dict[str, Any]:
        """获取所有条目（精简信息）"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute("""
                SELECT
                    id, class_type, name, customize_name, tags,
                    rating, favorite, disliked, created_at
                FROM prompt_items
            """)
            data = [dict_from_row(row) for row in cursor.fetchall()]
            return {"data": data}

    @staticmethod
    async def add_item(data: dict) -> Dict[str, Any]:
        """添加新条目"""
        class_type = data.get('class_type')
        if not class_type:
            raise web.HTTPBadRequest(reason="分类类型是必须的")

        with db_manager.get_connection() as conn:
            try:
                # 准备插入数据
                name = data.get('name')
                customize_name = data.get('customize_name')
                image = data.get('image', None)
                tags = data.get('tags', None)
                description = data.get('description', None)
                rating = data.get('rating', 0.0)
                favorite = 1 if data.get('favorite', False) else 0
                disliked = 1 if data.get('disliked', False) else 0

                cursor = conn.execute("""
                    INSERT INTO prompt_items (
                        id, class_type, name, customize_name, image,
                        tags, description, rating, favorite, disliked
                    ) VALUES (
                        hex(randomblob(16)), ?, ?, ?, ?, ?, ?, ?, ?, ?
                    )
                """, [
                    class_type, name, customize_name, image,
                    tags, description, rating, favorite, disliked
                ])

                # 获取新创建的条目
                new_item = conn.execute(
                    'SELECT * FROM prompt_items WHERE rowid = ?',
                    [cursor.lastrowid]
                ).fetchone()

                return {
                    "result": {
                        "id": new_item['id'],
                        "changes": cursor.rowcount
                    }
                }
            except sqlite3.IntegrityError as e:
                raise web.HTTPConflict(reason=str(e))

    @staticmethod
    async def get_items_paged(
            page: int,
            page_size: int,
            class_type: Optional[str],
            name: Optional[str],
            customize_name: Optional[str],
            tags: Optional[str],
            min_rating: Optional[float],
            max_rating: Optional[float],
            favorite: Optional[str],
            disliked: Optional[str],
            item_id: Optional[str],
            sort_field: str,
            sort_order: str
    ) -> Dict[str, Any]:
        """条目分页查询（增强版）"""
        # 验证排序字段
        allowed_fields = [
            'created_at', 'updated_at', 'name', 'customize_name', 'rating'
        ]
        order_by = sort_field if sort_field in allowed_fields else 'updated_at'
        order_dir = sort_order.upper() if sort_order.upper() in ['ASC', 'DESC'] else 'DESC'

        where: List[str] = []
        params: List[Any] = []

        # 构建查询条件
        if class_type and class_type != '':
            where.append('class_type = ?')
            params.append(class_type)

        if item_id:
            where.append('id = ?')
            params.append(item_id)

        if name:
            where.append('name LIKE ?')
            params.append(f'%{name}%')

        if customize_name:
            where.append('customize_name LIKE ?')
            params.append(f'%{customize_name}%')

        if tags:
            where.append('tags LIKE ?')
            params.append(f'%{tags}%')

        if min_rating is not None:
            where.append('rating >= ?')
            params.append(min_rating)

        if max_rating is not None:
            where.append('rating <= ?')
            params.append(max_rating)

        if favorite is not None and favorite != '':
            where.append('favorite = ?')
            params.append(favorite)

        if disliked is not None and disliked != '':
            where.append('disliked = ?')
            params.append(disliked)

        where_clause = f'WHERE {" AND ".join(where)}' if where else ''
        offset = (page - 1) * page_size

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row

            # 查询数据
            data_cursor = conn.execute(f"""
                SELECT
                    id, class_type, name, customize_name, image,
                    tags, rating, favorite, disliked, created_at
                FROM prompt_items
                {where_clause}
                ORDER BY {order_by} {order_dir}
                LIMIT ? OFFSET ?
            """, params + [page_size, offset])

            data = [dict_from_row(row) for row in data_cursor.fetchall()]

            # 查询总数
            total_cursor = conn.execute(
                f'SELECT COUNT(*) AS total FROM prompt_items {where_clause}',
                params
            )
            total = total_cursor.fetchone()['total']

            return {
                "data": data,
                "pagination": {
                    "total": total,
                    "page": page,
                    "page_size": page_size,
                    "total_pages": (total + page_size - 1) // page_size
                }
            }

    @staticmethod
    async def get_random_item(
            class_type: Optional[str],
            favorite: Optional[bool],
            disliked: Optional[bool],
            value: Optional[List[float]]
    ) -> Dict[str, Any]:
        """随机获取条目（支持按分类过滤）"""
        query = 'SELECT * FROM prompt_items'
        conditions: List[str] = []
        params: List[Any] = []

        # 添加 class_type 条件
        if class_type:
            conditions.append('class_type = ?')
            params.append(class_type)

        # 添加 favorite 条件
        if favorite is not None:
            conditions.append('favorite = ?')
            params.append(1 if favorite else 0)

        # 添加 disliked 条件
        if disliked is not None:
            conditions.append('disliked = ?')
            params.append(1 if disliked else 0)

        # 处理评分范围条件
        if isinstance(value, list) and len(value) == 2:
            min_val, max_val = value
            if min_val > 0:
                conditions.append('rating >= ?')
                params.append(min_val)
            if max_val > 0:
                conditions.append('rating <= ?')
                params.append(max_val)

        # 组合所有条件
        if conditions:
            query += ' WHERE ' + ' AND '.join(conditions)

        query += ' ORDER BY RANDOM() LIMIT 1'

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            result = conn.execute(query, params).fetchone()

            if not result:
                return {"result": None}

            return {"result": dict_from_row(result)}

    @staticmethod
    async def get_adjacent_item(
            current_id: str,
            direction: str,
            value: Optional[List[float]],
            req_class_type: Optional[str]
    ) -> Dict[str, Any]:
        """获取相邻记录"""
        # 特殊处理：当 current_id 为 "" 时，返回第一条记录
        if current_id == "":
            if not req_class_type:
                raise web.HTTPBadRequest(reason='当 current_id 为 "" 时，必须提供 class_type 参数')

            # 构建条件
            conditions: List[str] = ['class_type = ?']
            params: List[Any] = [req_class_type]

            # 添加评分范围条件
            if isinstance(value, list) and len(value) == 2:
                min_val, max_val = value
                if min_val > 0:
                    conditions.append('rating >= ?')
                    params.append(min_val)
                if max_val > 0:
                    conditions.append('rating <= ?')
                    params.append(max_val)

            # 查询第一条记录
            query = f"""
                SELECT * FROM prompt_items
                WHERE {' AND '.join(conditions)}
                ORDER BY updated_at DESC, id ASC
                LIMIT 1
            """

            with db_manager.get_connection() as conn:
                conn.row_factory = sqlite3.Row
                result = conn.execute(query, params).fetchone()
                return {"result": dict_from_row(result) or None}

        # 获取当前记录信息
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            current_record = conn.execute(
                'SELECT class_type, updated_at, rating FROM prompt_items WHERE id = ?',
                [current_id]
            ).fetchone()

            if not current_record:
                raise web.HTTPNotFound(reason="当前记录不存在")

            # 确定分类类型
            class_type = req_class_type if req_class_type else current_record['class_type']
            updated_at = current_record['updated_at']

            # 构建查询参数
            conditions: List[str] = ['class_type = ?']
            params: List[Any] = [class_type]

            # 添加评分范围条件
            if isinstance(value, list) and len(value) == 2:
                min_val, max_val = value
                if min_val > 0:
                    conditions.append('rating >= ?')
                    params.append(min_val)
                if max_val > 0:
                    conditions.append('rating <= ?')
                    params.append(max_val)

            # 添加方向条件
            if direction == 'prev':
                conditions.append('(updated_at < ? OR (updated_at = ? AND id < ?))')
                params.extend([updated_at, updated_at, current_id])
                adjacent_query = f"""
                    SELECT * FROM prompt_items
                    WHERE {' AND '.join(conditions)}
                    ORDER BY updated_at DESC, id DESC
                    LIMIT 1
                """
            else:  # 'next'
                conditions.append('(updated_at > ? OR (updated_at = ? AND id > ?))')
                params.extend([updated_at, updated_at, current_id])
                adjacent_query = f"""
                    SELECT * FROM prompt_items
                    WHERE {' AND '.join(conditions)}
                    ORDER BY updated_at ASC, id ASC
                    LIMIT 1
                """

            # 执行相邻记录查询
            adjacent_item = conn.execute(adjacent_query, params).fetchone()

            # 边界处理：无相邻记录时返回第一条
            if not adjacent_item:
                first_item_query = f"""
                    SELECT * FROM prompt_items
                    WHERE {' AND '.join(conditions)}
                    ORDER BY updated_at DESC, id ASC
                    LIMIT 1
                """
                adjacent_item = conn.execute(first_item_query, params).fetchone()

            return {"result": dict_from_row(adjacent_item) or None}

    @staticmethod
    async def batch_update_items(ids: List[str], updates: dict) -> Dict[str, Any]:
        """批量更新评分/收藏状态"""
        # 允许更新的字段列表
        allowed_fields = [
            'title', 'content', 'class_type', 'rating',
            'favorite', 'disliked', 'usage_count', 'tags'
        ]

        # 过滤掉不允许的字段
        valid_updates = {k: v for k, v in updates.items() if k in allowed_fields and k != 'id'}

        if not valid_updates:
            raise web.HTTPBadRequest(reason=f'至少需要提供一个可更新字段: {", ".join(allowed_fields)}')

        # 构建SET子句和参数
        set_clauses = ['updated_at = CURRENT_TIMESTAMP']
        params: List[Any] = []

        for key, value in valid_updates.items():
            # 处理布尔值转换
            db_value = 1 if value else 0 if key in ['favorite', 'disliked'] else value
            set_clauses.append(f"{key} = ?")
            params.append(db_value)

        # 构建ID列表参数
        id_placeholders = ', '.join(['?'] * len(ids))
        params.extend(ids)

        with db_manager.get_connection() as conn:
            try:
                cursor = conn.execute(f"""
                    UPDATE prompt_items
                    SET {', '.join(set_clauses)}
                    WHERE id IN ({id_placeholders})
                """, params)

                return {
                    "updated": cursor.rowcount,
                    "updatedFields": list(valid_updates.keys())
                }
            except sqlite3.IntegrityError as e:
                raise web.HTTPConflict(reason=str(e))

    @staticmethod
    async def get_items_by_class(class_type: Optional[str]) -> Dict[str, Any]:
        """按分类获取条目"""
        query = """
            SELECT
                id,
                name,
                customize_name,
                tags
            FROM prompt_items
        """
        params: List[Any] = []

        # 如果有class_type参数，添加WHERE条件
        if class_type:
            query += ' WHERE class_type = ?'
            params.append(class_type)

        # 添加默认排序
        query += ' ORDER BY updated_at DESC'

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(query, params)
            data = [dict_from_row(row) for row in cursor.fetchall()]
            return {"data": data}

    @classmethod
    async def batch_add_items(cls, request: web.Request) -> web.Response:
        """处理多文件上传，批量导入提示条目数据，带完整进度跟踪"""
        errors = []
        task_id = None
        try:
            data = await request.post()

            # 获取前端传入的任务ID
            task_id = data.get('taskId')
            if not task_id or not isinstance(task_id, str):
                return web.json_response({
                    "success": False,
                    "message": "缺少有效的任务ID（taskId）"
                }, status=400)

            # 获取分类类型（批量导入的所有条目属于同一分类）
            class_type = data.get('classType')
            if not class_type or not isinstance(class_type, str):
                return web.json_response({
                    "success": False,
                    "message": "缺少有效的分类类型（classType）"
                }, status=400)

            image_files, text_files = cls._separate_files(data)

            # 获取并验证目标路径参数
            target_dir_str = data.get('targetPath')
            if not target_dir_str or not isinstance(target_dir_str, str):
                errors.append("目标路径不存在或无效，请提供有效的targetPath参数")
                return web.json_response({
                    "success": False,
                    "message": "未处理到任何有效的提示条目数据",
                    "errors": errors
                }, status=400)

            if not image_files:
                errors.append("未找到任何图片文件，请检查上传的文件是否包含支持的图片格式")
                return web.json_response({
                    "success": False,
                    "message": "未处理到任何有效的提示条目数据",
                    "errors": errors
                }, status=400)

            # 初始化任务进度（总任务数为图片数量 + 1，加1用于数据库插入步骤）
            total_image_tasks = len(image_files)
            total_tasks = total_image_tasks + 1  # 额外加1步数据库插入
            task_manager.create_task(task_id, total_tasks)

            # 注册取消任务的回调
            def cancel_import():
                print(f"任务 {task_id} 被取消")
                # 可以在这里添加资源清理逻辑

            task_manager.add_active_export(task_id, cancel_import)

            if not text_files:
                errors.append("未找到任何文本文件，将使用默认标签和描述")

            # 处理文本文件，获取标签和描述信息
            item_info_map = {}
            if text_files:
                try:
                    item_info_map = await cls._process_text_files(text_files)
                except Exception as e:
                    err_msg = f"处理文本文件时出错: {str(e)}"
                    errors.append(err_msg)
                    print(f"文本文件处理错误: {traceback.format_exc()}")

            # 处理图片文件，生成提示条目数据
            prompt_items = []
            try:
                # 传递任务ID用于进度更新
                prompt_items = await cls._process_image_files(
                    image_files, item_info_map, errors, target_dir_str,
                    task_id, total_image_tasks, class_type
                )
            except Exception as e:
                err_msg = f"处理图片文件时出错: {str(e)}"
                errors.append(err_msg)
                print(f"图片文件处理错误: {traceback.format_exc()}")
                # 标记任务失败
                await task_manager.emit('error', task_id, e)
                task_manager.cancel_task(task_id)
                return web.json_response({
                    "success": False,
                    "message": err_msg,
                    "errors": errors
                }, status=500)

            if not prompt_items:
                task_manager.complete_task(task_id)
                return web.json_response({
                    "success": False,
                    "message": "未处理到任何有效的提示条目数据",
                    "errors": errors
                }, status=400)

            # 图片处理完成后，更新进度到图片数量（接近完成）
            task_manager.update_progress(task_id, total_image_tasks)
            await asyncio.sleep(0.01)  # 给前端一点时间接收进度更新

            # 执行数据库插入并跟踪进度
            try:
                result = await cls.bulk_add_items(prompt_items)

                # 数据库插入完成，更新进度到100%
                task_manager.update_progress(task_id, total_tasks)
                await asyncio.sleep(0.01)
            except Exception as e:
                err_msg = f"数据库插入失败: {str(e)}"
                errors.append(err_msg)
                print(f"数据库插入错误: {traceback.format_exc()}")
                await task_manager.emit('error', task_id, e)
                task_manager.cancel_task(task_id)
                return web.json_response({
                    "success": False,
                    "message": err_msg,
                    "errors": errors
                }, status=500)

            # 标记任务完成
            task_manager.complete_task(task_id)

            return web.json_response({
                "success": True,
                "message": f"成功导入 {result} 个提示条目",
                "count": result,
                "warnings": errors
            })

        except Exception as e:
            error_msg = str(e).replace('\n', ' ').replace('\r', ' ')
            full_error = traceback.format_exc()
            print(f"批量添加提示条目失败: {full_error}")

            # 如果有任务ID，标记任务失败
            if task_id:
                await task_manager.emit('error', task_id, e)
                task_manager.cancel_task(task_id)

            return web.json_response({
                "success": False,
                "message": f"处理失败: {error_msg}",
                "errors": errors + [full_error.split('\n')[-2]]
            }, status=400)

    @classmethod
    def _separate_files(cls, data: Dict[str, Any]) -> Tuple[List[Any], List[Any]]:
        """分离图片文件和文本文件，适配不同的文件上传对象"""
        image_files = []
        text_files = []

        # 处理aiohttp的MultiDict格式，确保正确提取文件
        for key, value in data.items():
            # 检查是否是文件字段（可能是单个文件或文件列表）
            if key == 'files':
                # 确保files是列表格式
                files = value if isinstance(value, list) else [value]

                for file in files:
                    if hasattr(file, 'filename') and file.filename:
                        filename = file.filename.lower()
                        ext = os.path.splitext(filename)[1]

                        if ext in cls.IMAGE_EXTENSIONS:
                            image_files.append(file)
                        elif ext in cls.TEXT_EXTENSIONS:
                            text_files.append(file)
                        else:
                            print(f"忽略不支持的文件类型: {filename} (扩展名: {ext})")

        if not image_files and not text_files:
            print("未找到有效的上传文件")

        return image_files, text_files

    @classmethod
    async def _process_text_files(cls, text_files: List[Any]) -> Dict[str, Dict[str, Any]]:
        """处理文本文件，提取内容并建立文件名到标签和描述的映射"""
        info_map = {}

        for file in text_files:
            try:
                base_name = os.path.splitext(file.filename)[0]
                file_ext = os.path.splitext(file.filename)[1].lower()

                # 区分同步和异步读取
                if hasattr(file, 'file'):
                    # 同步读取（返回bytes，不需要await）
                    content = file.file.read()
                elif hasattr(file, 'read'):
                    # 检查是否为协程函数
                    if inspect.iscoroutinefunction(file.read):
                        content = await file.read()  # 异步读取
                    else:
                        content = file.read()  # 同步读取
                else:
                    # 最后的同步读取方案
                    content = file.read()

                try:
                    text_content = content.decode('utf-8')
                except UnicodeDecodeError:
                    try:
                        text_content = content.decode('gbk')
                    except UnicodeDecodeError:
                        text_content = content.decode('latin-1', errors='replace')

                # 简单解析：假设第一行为标签，后续为描述
                lines = [line.strip() for line in text_content.split('\n') if line.strip()]
                tags = lines[0] if lines else ""
                description = '\n'.join(lines[1:]) if len(lines) > 1 else ""

                info_map[base_name] = {
                    'tags': tags,
                    'description': description,
                    'customize_name': base_name  # 默认使用文件名作为自定义名称
                }
                print(f"成功处理文本文件: {file.filename}")
            except Exception as e:
                print(f"处理文本文件 {file.filename} 失败: {str(e)}")
                continue

        return info_map

    @classmethod
    async def _process_image_files(
            cls,
            image_files: List[Any],
            item_info_map: Dict[str, Dict[str, Any]],
            errors: List[str],
            target_dir_str: str,
            task_id: str,
            total_image_tasks: int,
            class_type: str
    ) -> List[Dict[str, Any]]:
        """处理图片文件，生成提示条目对象列表，同时更新任务进度"""
        prompt_items = []
        processed = 0

        for file in image_files:
            try:
                # 检查任务是否已被取消
                if task_id not in task_manager.tasks:
                    raise Exception(f"任务 {task_id} 已被取消")

                filename = file.filename
                name = os.path.splitext(filename)[0]
                file_ext = os.path.splitext(filename)[1].lower()
                # 从信息映射中获取标签和描述，没有则使用默认值
                item_info = item_info_map.get(name, {})
                image_id = cls.generate_uuid_hex()
                # 保存图片并获取路径
                image_path = await cls._save_image_file(file, target_dir_str,image_id)

                if not image_path or not await cls.file_exists(image_path):
                    err_msg = f"图片保存失败: {filename}"
                    errors.append(err_msg)
                else:
                    # 初始标签值
                    tags = item_info.get('tags', '')

                    # 如果标签为空且是PNG图片，尝试从元数据读取
                    if not tags and file_ext == '.png':
                        try:
                            # 重新读取文件内容用于元数据解析
                            if hasattr(file, 'file'):
                                # 对于已读取过的文件，需要重新定位到开头
                                file.file.seek(0)
                                image_data = file.file.read()
                            elif hasattr(file, 'read'):
                                # 检查是否为协程函数
                                if inspect.iscoroutinefunction(file.read):
                                    # 对于异步读取，先确保回到开头
                                    await file.seek(0)
                                    image_data = await file.read()
                                else:
                                    file.seek(0)
                                    image_data = file.read()
                            else:
                                # 最后的方案
                                image_data = file.read()

                            # 使用ImageService读取元数据
                            with Image.open(BytesIO(image_data)) as img:
                                metadata = ImageService.extract_image_metadata(img)

                                # 解析ComfyUI元数据
                                comfy_prompt = metadata.get('prompt')
                                if comfy_prompt:
                                    parsed_result = ImageService.parse_comfyui_metadata(
                                        comfy_prompt, ImageService.default_node_field_map
                                    )
                                    if parsed_result['success']:
                                        tags = parsed_result['prompt']
                                    else:
                                        tags = f"解析提示词失败: {parsed_result.get('error', '未知错误')}"
                                else:
                                    # 尝试解析其他类型的元数据
                                    sd_metadata = metadata.get('parameters')
                                    if sd_metadata:
                                        tags = sd_metadata.strip()
                                    else:
                                        a1111_metadata = metadata.get('UserComment')
                                        if a1111_metadata and 'Steps:' in a1111_metadata:
                                            tags = a1111_metadata.strip()
                                        else:
                                            tags = "未找到可识别的元数据"

                            print(f"从PNG元数据提取标签: {filename}")
                        except Exception as e:
                            err_msg = f"读取PNG元数据失败: {filename}, 错误: {str(e)}"
                            errors.append(err_msg)
                            print(f"{err_msg}, 详细信息: {traceback.format_exc()}")
                            # 保持tags为空或使用默认值
                            tags = ""

                    # 创建提示条目标数据
                    prompt_item = {
                        'id': image_id,
                        'class_type': class_type,
                        'name': name,
                        'customize_name': item_info.get('customize_name', name),
                        'image': image_path,
                        'tags': tags,
                        'description': item_info.get('description', ''),
                        'rating': 0.0,
                        'favorite': False,
                        'disliked': False
                    }
                    prompt_items.append(prompt_item)
                    print(f"成功处理图片文件: {filename}")

                # 每处理完一个文件就更新进度
                processed += 1
                task_manager.update_progress(task_id, processed)

                # 短暂休眠以允许事件循环处理进度更新事件
                await asyncio.sleep(0.01)

            except Exception as e:
                err_msg = f"处理文件 {file.filename} 失败: {str(e)}"
                errors.append(err_msg)
                print(f"{err_msg}, 详细信息: {traceback.format_exc()}")

                # 即使出错也更新进度，确保进度能继续推进
                processed += 1
                task_manager.update_progress(task_id, processed)
                continue

        return prompt_items

    @classmethod
    async def _save_image_file(cls, file: Any, target_dir_str: str, image_id: str) -> str:
        """保存图片文件到指定目录并转换为webp格式"""
        try:
            # 验证目标目录参数
            if not target_dir_str or not isinstance(target_dir_str, str):
                raise ValueError("目标路径不存在或无效")

            # 解码URL编码的路径
            decoded_path = unquote(target_dir_str)

            # 处理不同格式的路径，确保它是目录
            if decoded_path.startswith("file://"):
                local_path = decoded_path.replace("file://", "")
                if local_path.startswith("/"):
                    local_path = local_path[1:]
                local_path = local_path.replace("/", "\\")  # 适配Windows路径
                target_dir = Path(local_path).resolve()
            else:
                # 处理绝对路径，确保它是目录
                target_dir = Path(decoded_path).resolve()

            target_filename = f"{image_id}.webp"
            # 组合目录和文件名得到完整路径
            target_path = target_dir / target_filename

            # 确保父目录存在
            if not await cls.file_exists(str(target_dir)):
                loop = asyncio.get_event_loop()
                await loop.run_in_executor(
                    None,
                    lambda: os.makedirs(target_dir, exist_ok=True, mode=0o755)
                )

            # 生成临时文件路径（用于原子操作）
            temp_file_name = f"{image_id}.tmp-{os.urandom(8).hex()}.webp"
            temp_path = target_dir / temp_file_name

            try:
                # 读取文件内容
                if hasattr(file, 'file'):
                    content = file.file.read()  # 同步读取
                elif hasattr(file, 'read'):
                    if inspect.iscoroutinefunction(file.read):
                        content = await file.read()  # 异步读取
                    else:
                        content = file.read()  # 同步读取
                else:
                    content = file.read()  # 最后的读取方案

                if not content:
                    raise ValueError("文件内容为空")

                # 转换图片为webp格式并保存到临时文件
                loop = asyncio.get_event_loop()
                await loop.run_in_executor(
                    None,
                    cls._convert_image,
                    content,
                    str(temp_path)
                )

                # 原子性重命名临时文件到目标路径
                await loop.run_in_executor(
                    None,
                    lambda: os.replace(str(temp_path), str(target_path))
                )

                abs_path = str(target_path.resolve())
                print(f"图片保存成功: {abs_path}")
                return abs_path

            except Exception as conversion_error:
                # 清理临时文件
                if await cls.file_exists(str(temp_path)):
                    try:
                        await loop.run_in_executor(
                            None,
                            lambda: os.unlink(str(temp_path))
                        )
                    except Exception as cleanup_error:
                        print(f"清理临时文件失败: {str(cleanup_error)}")
                raise ValueError(f"图片处理失败: {str(conversion_error)}")

        except Exception as e:
            print(f"保存图片失败: {str(e)}, 详细信息: {traceback.format_exc()}")
            raise

    @staticmethod
    def _convert_image(image_data: bytes, output_path: str):
        """转换图片为WebP格式（同步方法，在 executor 中运行）"""
        try:
            with Image.open(BytesIO(image_data)) as img:
                # 处理透明背景
                if img.mode in ('RGBA', 'LA'):
                    # 创建白色背景
                    background = Image.new(img.mode[:-1], img.size, (255, 255, 255))
                    # 粘贴带有透明度的图片
                    background.paste(img, img.split()[-1])
                    img = background
                elif img.mode == 'P':
                    # 处理索引色模式
                    img = img.convert('RGB')

                # 保存为WebP格式，质量80（平衡质量和文件大小）
                img.save(output_path, 'WebP', quality=80)
        except Exception as e:
            raise ValueError(f"图片转换错误: {str(e)}")

    @staticmethod
    async def file_exists(file_path: Path | str) -> bool:
        """优化后的异步检查文件是否存在（兼容str和Path类型）"""
        try:
            # 先将输入转换为Path（无论原始类型是str还是Path）
            path = Path(file_path) if isinstance(file_path, str) else file_path
            return await asyncio.to_thread(path.exists)
        except Exception:
            # 发生异常时默认返回不存在（例如权限不足）
            return False

    @classmethod
    async def bulk_add_items(cls, items: List[Dict[str, Any]]) -> int:
        """批量添加提示条目到数据库"""
        if not items:
            return 0

        with db_manager.get_connection() as conn:
            try:
                insert_data = []
                for item in items:
                    insert_data.append([
                        item['id'],
                        item['class_type'],
                        item['name'],
                        item['customize_name'],
                        item['tags'],
                        item['rating'],
                        1 if item['favorite'] else 0,
                        1 if item['disliked'] else 0
                    ])

                cursor = conn.executemany("""
                        INSERT INTO prompt_items (
                            id, class_type, name, customize_name,
                            tags, rating, favorite, disliked
                        ) VALUES (
                            ?, ?, ?, ?, ?, ?, ?, ?
                        )
                    """, insert_data)

                conn.commit()
                print(f"成功批量插入 {cursor.rowcount} 个提示条目")
                return cursor.rowcount

            except sqlite3.IntegrityError as e:
                conn.rollback()
                error_msg = str(e).replace('\n', ' ').replace('\r', ' ')
                raise ValueError(f"数据完整性错误: {error_msg}")
            except Exception as e:
                conn.rollback()
                error_msg = str(e).replace('\n', ' ').replace('\r', ' ')
                raise ValueError(f"插入数据失败: {error_msg}")


# 路由处理函数
async def handle_get_prompt_item_image(request: web.Request) -> web.Response:
    data = await request.json()
    item_id = data.get('id')
    if not item_id or not isinstance(item_id, str):
        return web.json_response({"error": "条目ID是必须的"}, status=400)

    try:
        result = await PromptItemService.get_image(item_id)
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_prompt_item(request: web.Request) -> web.Response:
    data = await request.json()
    item_id = data.get('id')
    if not item_id or not isinstance(item_id, str):
        return web.json_response({"error": "条目ID是必须的"}, status=400)

    try:
        result = await PromptItemService.delete_item(item_id)
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)

async def handle_delete_disliked_prompt_item(request: web.Request) -> web.Response:
    try:
        result = await PromptItemService.delete_disliked_item()
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_update_prompt_item(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        result = await PromptItemService.update_item(data)
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_prompt_items(request: web.Request) -> web.Response:
    try:
        result = await PromptItemService.get_all_items()
        return web.json_response(result)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_add_prompt_item(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        result = await PromptItemService.add_item(data)
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_prompt_items_paged(request: web.Request) -> web.Response:
    try:
        # 解析查询参数
        page = int(request.query.get('page', 1))
        page_size = int(request.query.get('page_size', 10))
        class_type = request.query.get('class_type')
        name = request.query.get('name')
        customize_name = request.query.get('customize_name')
        tags = request.query.get('tags')
        min_rating = float(request.query.get('min_rating')) if request.query.get('min_rating') else None
        max_rating = float(request.query.get('max_rating')) if request.query.get('max_rating') else None
        favorite = request.query.get('favorite')
        disliked = request.query.get('disliked')
        item_id = request.query.get('id')
        sort_field = request.query.get('sort_field', 'created_at')
        sort_order = request.query.get('sort_order', 'DESC')

        # 验证分页参数
        if page < 1 or page_size < 1 or page_size > 100:
            return web.json_response({
                "error": "页码必须大于0，页大小必须在1-100之间"
            }, status=400)

        result = await PromptItemService.get_items_paged(
            page, page_size, class_type, name, customize_name, tags,
            min_rating, max_rating, favorite, disliked, item_id,
            sort_field, sort_order
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_random_prompt_item(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        rating_range = RatingRange(**data)

        result = await PromptItemService.get_random_item(
            class_type=data.get('class_type'),
            favorite=data.get('favorite'),
            disliked=data.get('disliked'),
            value=rating_range.value
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_adjacent_prompt_item(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        req_data = AdjacentRequest(**data)

        result = await PromptItemService.get_adjacent_item(
            current_id=req_data.current_id,
            direction=req_data.direction,
            value=req_data.value,
            req_class_type=req_data.class_type
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_batch_update_prompt_items(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        # 验证请求数据
        batch_request = BatchUpdateRequest(**data)
        # 提取更新字段（排除ids）
        updates = {k: v for k, v in data.items() if k != 'ids'}

        result = await PromptItemService.batch_update_items(
            batch_request.ids,
            updates
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_prompt_items_by_class(request: web.Request) -> web.Response:
    try:
        class_type = request.query.get('class_type')
        result = await PromptItemService.get_items_by_class(class_type)
        return web.json_response(result)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_prompt_items_by_filter(request: web.Request) -> web.Response:
    """按筛选条件删除所有符合条件的prompt_items记录"""
    try:
        # 解析查询参数（与get_prompt_items_paged相同）
        class_type = request.query.get('class_type')
        name = request.query.get('name')
        customize_name = request.query.get('customize_name')
        tags = request.query.get('tags')
        min_rating = float(request.query.get('min_rating')) if request.query.get('min_rating') else None
        max_rating = float(request.query.get('max_rating')) if request.query.get('max_rating') else None
        favorite = request.query.get('favorite')
        disliked = request.query.get('disliked')
        item_id = request.query.get('id')

        # 构建查询条件
        where: List[str] = []
        params: List[Any] = []

        # 构建查询条件（与get_items_paged保持一致）
        if class_type:
            where.append('class_type = ?')
            params.append(class_type)

        if item_id:
            where.append('id = ?')
            params.append(item_id)

        if name:
            where.append('name LIKE ?')
            params.append(f'%{name}%')

        if customize_name:
            where.append('customize_name LIKE ?')
            params.append(f'%{customize_name}%')

        if tags:
            where.append('tags LIKE ?')
            params.append(f'%{tags}%')

        if min_rating is not None:
            where.append('rating >= ?')
            params.append(min_rating)

        if max_rating is not None:
            where.append('rating <= ?')
            params.append(max_rating)

        if favorite is not None and favorite != '':
            where.append('favorite = ?')
            params.append(favorite)

        if disliked is not None and disliked != '':
            where.append('disliked = ?')
            params.append(disliked)

        # 如果没有任何筛选条件，拒绝删除所有记录
        if not where:
            return web.json_response({
                "error": "删除操作必须提供至少一个筛选条件，以防止误删除所有记录"
            }, status=400)

        where_clause = f'WHERE {" AND ".join(where)}' if where else ''

        # 执行删除操作
        with db_manager.get_connection() as conn:
            # 先查询符合条件的记录数量
            count_cursor = conn.execute(
                f'SELECT COUNT(*) AS total FROM prompt_items {where_clause}',
                params
            )
            total = count_cursor.fetchone()[0]

            if total == 0:
                return web.json_response({
                    "success": True,
                    "message": "未找到符合条件的记录",
                    "deleted_count": 0
                })

            # 执行删除
            delete_cursor = conn.execute(
                f'DELETE FROM prompt_items {where_clause}',
                params
            )
            conn.commit()

            return web.json_response({
                "success": True,
                "message": f"成功删除 {delete_cursor.rowcount} 条记录",
                "deleted_count": delete_cursor.rowcount
            })

    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)

# 路由配置
routes = [
    web.post("/sql/get_prompt_item_image", handle_get_prompt_item_image),
    web.post("/sql/delete_prompt_item", handle_delete_prompt_item),
    web.post("/sql/delete_disliked_prompt_item", handle_delete_disliked_prompt_item),
    web.delete("/sql/delete_prompt_items_by_filter", handle_delete_prompt_items_by_filter),
    web.put("/sql/update_prompt_item", handle_update_prompt_item),
    web.get("/sql/get_prompt_items", handle_get_prompt_items),
    web.post("/sql/add_prompt_item", handle_add_prompt_item),
    web.post("/sql/batch_add_items", PromptItemService.batch_add_items),
    web.get("/sql/get_prompt_items_paged", handle_get_prompt_items_paged),
    web.post("/sql/get_random_prompt_item", handle_get_random_prompt_item),
    web.post("/sql/get_adjacent_prompt_item", handle_get_adjacent_prompt_item),
    web.post("/sql/batch_update_prompt_items", handle_batch_update_prompt_items),
    web.get("/sql/get_prompt_items_by_class", handle_get_prompt_items_by_class),
]
